package tt.dz.biz.dao.financial;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.vo.MerchantVO;
import tt.dz.vo.SubjectVO;

@Repository("subjectDAO")
public class SubjectDAOImpl
  extends BaseDAO
{
  private static final String QUERY_SUBJECT = "select  a.subject_code,a.subject_name,a.parent_subject_code,a.is_valid,a.subject_type  from ms_subject as a ";
  private static final String INSERT_SUBJECT = "INSERT INTO ms_subject(subject_code,subject_name,parent_subject_code,is_valid,subject_type) VALUES (:subjectCode,:subjectName,:parentSubjectCode,:isValid,:subjectType);";
  private static final String UPDATE_SUBJECT = "UPDATE ms_subject SET subject_name = :subjectName,parent_subject_code = :parentSubjectCode,is_valid = :isValid,subject_type = :subjectType \twhere subject_code = :subjectCode;";
  private static final String QUERY_ALL_MERCHANT = "SELECT merchant_code FROM ms_merchant where is_valid = 1";
  private static final String INSERT_MERCHANT_SUBJECT = "INSERT INTO ms_merchant_subject_acc(merchant_code,sub_subject_code,acct_bal) VALUES (:merchantCode, :subjectCode,0);";
  private static final String QUERY_CHANNEL_SUBJECT = "select  a.subject_code,a.subject_name,a.parent_subject_code,a.is_valid,a.subject_type  from ms_subject as a  where (a.parent_subject_code = '1001' or a.parent_subject_code = '1002') and a.is_valid = 1";
  private static final String PRODUCT_SUBJECT_CODE = "select max(sj.subject_code) from ms_subject sj where  sj.parent_subject_code = :parentSubjectCode";
  
  public Integer productSubjectCode(SubjectVO subjectVO)
  {
    SqlParameterSource paramMap = new BeanPropertySqlParameterSource(subjectVO);
    String sujectCode = (String)this.jdbcTemplate.queryForObject("select max(sj.subject_code) from ms_subject sj where  sj.parent_subject_code = :parentSubjectCode", paramMap, String.class);
    return Integer.valueOf(Integer.valueOf(sujectCode).intValue() + 1);
  }
  
  public List<SubjectVO> queryChannelSubject()
  {
    return queryForList("select  a.subject_code,a.subject_name,a.parent_subject_code,a.is_valid,a.subject_type  from ms_subject as a  where (a.parent_subject_code = '1001' or a.parent_subject_code = '1002') and a.is_valid = 1", null, SubjectVO.class);
  }
  
  public List<SubjectVO> querySubject()
  {
    return queryForList("select  a.subject_code,a.subject_name,a.parent_subject_code,a.is_valid,a.subject_type  from ms_subject as a  order by a.subject_code ", null, SubjectVO.class);
  }
  
  public SubjectVO querySubjectByCode(String subjectCode)
  {
    try
    {
      StringBuilder sql = new StringBuilder("select  a.subject_code,a.subject_name,a.parent_subject_code,a.is_valid,a.subject_type  from ms_subject as a ");
      sql.append(" where  subject_code=:subjectCode");
      SubjectVO param = new SubjectVO();
      param.setSubjectCode(subjectCode);
      return (SubjectVO)queryForObject(sql.toString(), param, SubjectVO.class);
    }
    catch (EmptyResultDataAccessException e) {}
    return null;
  }
  
  public int insertSubject(SubjectVO subjectVO)
  {
    return save("INSERT INTO ms_subject(subject_code,subject_name,parent_subject_code,is_valid,subject_type) VALUES (:subjectCode,:subjectName,:parentSubjectCode,:isValid,:subjectType);", subjectVO);
  }
  
  public void insertMerchantSubject(SubjectVO subjectVO)
  {
    List<MerchantVO> merchantList = queryForList("SELECT merchant_code FROM ms_merchant where is_valid = 1", null, MerchantVO.class);
    List<Map<String, String>> batchValues = new ArrayList();
    Iterator<MerchantVO> iterator = merchantList.iterator();
    Map<String, String> paramValue = null;
    while (iterator.hasNext())
    {
      paramValue = new HashMap();
      paramValue.put("merchantCode", ((MerchantVO)iterator.next()).getMerchantCode());
      paramValue.put("subjectCode", subjectVO.getSubjectCode());
      batchValues.add(paramValue);
    }
    getJdbcTemplate().batchUpdate("INSERT INTO ms_merchant_subject_acc(merchant_code,sub_subject_code,acct_bal) VALUES (:merchantCode, :subjectCode,0);", (Map[])batchValues.toArray(new Map[merchantList.size()]));
  }
  
  public int updateSubject(SubjectVO subjectVO)
  {
    return save("UPDATE ms_subject SET subject_name = :subjectName,parent_subject_code = :parentSubjectCode,is_valid = :isValid,subject_type = :subjectType \twhere subject_code = :subjectCode;", subjectVO);
  }
  
  public List<SubjectVO> querySubjectByNameAndParentCode(SubjectVO subjectVO)
  {
    StringBuilder sql = new StringBuilder("select  a.subject_code,a.subject_name,a.parent_subject_code,a.is_valid,a.subject_type  from ms_subject as a ");
    sql.append(" where  subject_name=:subjectName  and parent_subject_code=:parentSubjectCode ");
    if (StringUtils.isNotBlank(subjectVO.getSubjectCode())) {
      sql.append(" and subject_code<>:subjectCode ");
    }
    return queryForList(sql.toString(), subjectVO, SubjectVO.class);
  }
}
